using System.Data;
using Microsoft.Extensions.Options;
using SPI.AgentServer.Configuration;
using SPI.AgentServer.Services.Execution;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 区域统计工具 - 获取区域统计信息
/// </summary>
public class GetAreaStatsTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly DatabaseConfiguration _config;
    private readonly ILogger<GetAreaStatsTool> _logger;

    public string Name => "get_area_stats";
    public string Description => "获取区域统计信息，包括区域总数、设备数量、告警数量等";

    public GetAreaStatsTool(
        IDatabaseExecutor executor,
        IOptions<DatabaseConfiguration> options,
        ILogger<GetAreaStatsTool> logger)
    {
        _executor = executor;
        _config = options.Value;
        _logger = logger;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (!ValidateParameters(parameters, out var errors))
        {
            return new ToolResult
            {
                Success = false,
                Errors = errors
            };
        }

        try
        {
            var areaId = parameters.ContainsKey("areaId") ? parameters["areaId"]?.ToString() : null;
            var includeChildren = parameters.ContainsKey("includeChildren")
                ? Convert.ToBoolean(parameters["includeChildren"])
                : false;

            string sql;

            if (string.IsNullOrEmpty(areaId))
            {
                // 查询所有区域的统计信息
                sql = @"
SELECT
    a.Id,
    a.Name,
    a.AreaType,
    a.FullName,
    a.IsKeyArea,
    a.NumberOfPeople,
    (SELECT COUNT(*) FROM tbldevice d WHERE d.StationAreaId = a.Id AND d.IsValid = 1) as DeviceCount,
    (SELECT COUNT(*) FROM tblalarmlog al WHERE (al.AreaId = a.Id OR al.StationAreaId = a.Id) AND al.IsValid = 1 AND DATEDIFF(day, al.AlarmCreateTime, GETDATE()) <= 7) as RecentAlarmCount
FROM tblarea a
WHERE a.IsValid = 1
ORDER BY a.Order";
            }
            else
            {
                // 查询特定区域的详细信息
                if (includeChildren)
                {
                    sql = $@"
WITH AreaTree AS (
    SELECT Id, Name, AreaType, FullName, ParentId, IsKeyArea, NumberOfPeople
    FROM tblarea
    WHERE Id = '{areaId}' AND IsValid = 1
    UNION ALL
    SELECT a.Id, a.Name, a.AreaType, a.FullName, a.ParentId, a.IsKeyArea, a.NumberOfPeople
    FROM tblarea a
    INNER JOIN AreaTree at ON a.ParentId = at.Id
    WHERE a.IsValid = 1
)
SELECT
    at.Id,
    at.Name,
    at.AreaType,
    at.FullName,
    at.IsKeyArea,
    at.NumberOfPeople,
    (SELECT COUNT(*) FROM tbldevice d WHERE d.StationAreaId = at.Id AND d.IsValid = 1) as DeviceCount,
    (SELECT COUNT(*) FROM tblalarmlog al WHERE (al.AreaId = at.Id OR al.StationAreaId = at.Id) AND al.IsValid = 1 AND DATEDIFF(day, al.AlarmCreateTime, GETDATE()) <= 7) as RecentAlarmCount
FROM AreaTree at";
                }
                else
                {
                    sql = $@"
SELECT
    a.Id,
    a.Name,
    a.AreaType,
    a.FullName,
    a.IsKeyArea,
    a.NumberOfPeople,
    (SELECT COUNT(*) FROM tbldevice d WHERE d.StationAreaId = a.Id AND d.IsValid = 1) as DeviceCount,
    (SELECT COUNT(*) FROM tblalarmlog al WHERE (al.AreaId = a.Id OR al.StationAreaId = a.Id) AND al.IsValid = 1 AND DATEDIFF(day, al.AlarmCreateTime, GETDATE()) <= 7) as RecentAlarmCount
FROM tblarea a
WHERE a.Id = '{areaId}' AND a.IsValid = 1";
                }
            }

            _logger.LogInformation("Executing area stats query: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = $"查询到 {result.RowCount} 个区域的统计信息",
                Metadata = new Dictionary<string, object>
                {
                    ["rowCount"] = result.RowCount,
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing area stats query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询错误: {ex.Message}" }
            };
        }
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        // 参数都是可选的
        return true;
    }
}
